# coding=utf-8
# 代码文件：Academicplatform/com/dao/file_dao.py

"""文档管理 DAO"""
from com.dao.base_dao import BaseDao


class FileDao(BaseDao):
    def __init__(self):
        super().__init__()

    def findall(self):
        """查询所有文档信息"""

        files = []
        try:
            # 确保连接有效
            if not self.conn or not self.conn.open:
                self.connect()  # 重新建立连接
            # 创建游标对象
            with self.conn.cursor() as cursor:
                # 执行 SQL 操作
                sql = 'select fileid,category,cname,ename,thumbnail,descn,author,journal,points ' \
                        'from files'
                cursor.execute(sql)
                # 提取结果集
                result_set = cursor.fetchall()

                for row in result_set:
                    file = {}
                    file['fileid'] = row[0]
                    file['category'] = row[1]
                    file['cname'] = row[2]
                    file['ename'] = row[3]
                    file['thumbnail'] = row[4]
                    file['descn'] = row[5]
                    file['author'] = row[6]
                    file['journal'] = row[7]
                    file['points'] = row[8]
                    files.append(file)  # 将 file 添加到 files 列表
                # with 代码块结束，关闭游标

        finally:
            # 关闭数据连接
            self.close()

        return files

    def findbycat(self, catname):
        """按照文档类别查询文档"""

        files = []
        try:
            # 确保连接有效
            if not self.conn or not self.conn.open:
                self.connect()  # 重新建立连接
            # 创建游标对象
            with self.conn.cursor() as cursor:
                # 执行 SQL 操作
                sql = 'select fileid,category,cname,ename,thumbnail,descn,author,journal,points ' \
                      'from files where category=%s'
                cursor.execute(sql, catname)
                # 提取结果集
                result_set = cursor.fetchall()

                for row in result_set:
                    file = {}
                    file['fileid'] = row[0]
                    file['category'] = row[1]
                    file['cname'] = row[2]
                    file['ename'] = row[3]
                    file['thumbnail'] = row[4]
                    file['descn'] = row[5]
                    file['author'] = row[6]
                    file['journal'] = row[7]
                    file['points'] = row[8]
                    files.append(file)  # 将 file 添加到 files 列表
                # with 代码块结束，关闭游标

        finally:
            # 关闭数据连接
            self.close()

        return files

    def findbyid(self, fileid):
        """按照文档id查询文档"""

        file = {}
        try:
            # 创建游标对象
            with self.conn.cursor() as cursor:
                # 执行 SQL 操作
                sql = 'select fileid,category,cname,ename,thumbnail,descn,author,journal,points ' \
                      'from files where fileid=%s'
                cursor.execute(sql, fileid)
                # 提取结果集
                row = cursor.fetchone()

                if row is not None:
                    file['fileid'] = row[0]
                    file['category'] = row[1]
                    file['cname'] = row[2]
                    file['ename'] = row[3]
                    file['thumbnail'] = row[4]
                    file['descn'] = row[5]
                    file['author'] = row[6]
                    file['journal'] = row[7]
                    file['points'] = row[8]
                # with 代码块结束，关闭游标

        finally:
            # 关闭数据连接
            self.close()

        return file
